package com.neusoft.chenhanhua.gqing;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

public class Cms_subject_category {
	//插入
		@Test
		public void insert() {
			Connection conn=null;
			String username="root";
			String password="root";
			String driver="com.mysql.jdbc.Driver";
			String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&charaterEncoding=utf-8";
			try {
				//加入驱动类
				Class.forName(driver);
				conn=DriverManager.getConnection(url,username,password);
			} catch (Exception e) {
				e.printStackTrace();
			}
			String sql="insert into Cms_subject_category(id,name,icon,subject_count,show_status,sort)values(9,'fd','dfd',6,3,4)";
			Statement st=null;
			try {
				//通过数据库连接conn获取事务java.sql.Statement对象st
				st=conn.createStatement();
				//执行sql命令
				int row=st.executeUpdate(sql);
				System.out.println(row>0?"添加成功":"添加失败");
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				if (st!=null) {
					try {
						st.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if (conn!=null) {
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
		//修改
		@Test
		public void update() {
			Connection conn=null;
			//准备数据库密码,用户名，url,driver
			String username="root";
			String passowrd="root";
			String driver="com.mysql.jdbc.Driver";
			String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
			try {
				//加载驱动类到虚拟机中
				Class.forName(driver);
				//连接数据库
				conn=DriverManager.getConnection(url,username,passowrd);
			} catch (Exception e) {
				e.printStackTrace();
			}
			//准备数据库命令id,name,sub_title,pic,sort,show_status
			String sql="update Cms_subject_category set name='这啊 'where id=2";
			Statement st=null;
			try {
				//获取connjava.sql。statement对象st
				st=conn.createStatement();
				int row=st.executeUpdate(sql);
				System.out.println(row>0?"修改成功":"修改失败");
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				if (conn!=null) {
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if (st!=null) {
					try {
						st.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
			
		}
		@Test
		public void delete() {
			Connection conn=null;
			String username="root";
			String passowrd="root";
			String driver="com.mysql.jdbc.Driver";
			String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&charaterEncoding=utf-8";
		    try {
		    	//加载驱动类到虚拟机
				Class.forName(driver);
				conn=DriverManager.getConnection(url,username,passowrd);
			} catch (Exception e) {
				e.printStackTrace();
			}
		    //准备SQL命令
		    String sql="delete from Cms_subject_category where id=1";
		    Statement st=null;
		    try {
				st=conn.createStatement();
				int row=st.executeUpdate(sql);
				System.out.println(row>0?"删除成功":"删除失败");
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				if (st!=null) {
					try {
						st.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if (conn!=null) {
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		    
			
		}
		//查询查一条
			@Test
			public void selet() {
				Connection conn = null;
				//1、准备数据库用户名、密码、url、driver
				String username = "root";
				String password = "root";
				String driver = "com.mysql.jdbc.Driver";
				//协议://地址:端口号/数据库名?连接属性设置
				String url = "jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
				
				try {
					//加载驱动类到虚拟机中
					Class.forName(driver);//这是属于反射的内容
					//获取数据库连接java.sql.Connection
					conn = DriverManager.getConnection(url, username, password);
				} catch (Exception e) {
					e.printStackTrace();
				}
				//准备SQL命令
						String sql = "select * from Cms_subject_category";
						Statement st = null;
						ResultSet rs = null;
						try {
							//通过数据库连接conn获取事务java.sql.Statement对象st
							st = conn.createStatement();
							//执行sql命令
							rs = st.executeQuery(sql);
							//处理查询结果集
							while(rs.next()) {
								//rs.getInt(columnLabel)：columnLabel列名或列的别名，推荐使用
								System.out.println(rs.getInt("id"));
								System.out.println(rs.getObject("name"));
								System.out.println(rs.getObject("icon"));
								System.out.println(rs.getObject("subject_count"));
								System.out.println(rs.getInt("show_status"));
								System.out.println(rs.getInt("sort"));
								//获取username列
								//System.out.println(rs.getString("username"));
							}
						} catch (SQLException e) {
							e.printStackTrace();
						} finally {
							if(rs != null) {
								try {
									rs.close();
								} catch (SQLException e) {
									e.printStackTrace();
								}
							}
							if(st != null) {
								try {
									st.close();
								} catch (SQLException e) {
									e.printStackTrace();
								}
							}
							if(conn != null) {
								try {
									conn.close();
								} catch (SQLException e) {
									e.printStackTrace();
								}
							}
			}
			}

}
